package it.free.maya.worddistinct;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;

/**
 * Connect the database and extract data for one field
 * @author Stefano Falconetti
 */
public class WordsDatabaseExtractor {

	private String driver = null;
	private String connectionUrl = null;
	private String username = null;
	private String password = null;
	private String query = null;
	private int queryTimeoutSeconds = 0;
	
	private Connection connection = null;
	
	private static Logger logger = Logger.getLogger(WordsDatabaseExtractor.class);
	
	/**
	 * Default class builder
	 */
	public WordsDatabaseExtractor(String driver,
								 String connUrl, 
								 String userName, 
								 String password,
								 String query,
								 int queryTimeoutSeconds) throws Exception{
		
		if( (driver == null) ||
			(connUrl == null) ||
			(userName == null) || 
			(password == null) ||
			(query == null) ){
			throw new Exception("Missing parameter, I can't continue");
		}
		
		this.driver = driver;
		this.connectionUrl = connUrl;
		this.username = userName;
		this.password = password;
		this.query = query;
		this.queryTimeoutSeconds = queryTimeoutSeconds;
	}
	
	/**
	 * Create an internal connection
	 * @throws Exception
	 */
	public void connect() throws Exception{
	
		logger.info("Connecting to [" + this.connectionUrl + "]");
		
		Class.forName(this.driver).newInstance();
		this.connection = DriverManager.getConnection(this.connectionUrl, 
		   											  this.username, 
		   											  this.password);
		    
		this.connection.setAutoCommit(false);
		this.connection.setReadOnly(true);
		logger.info("Connected");
	}
	

	/**
	 * Close and release an internal connection
	 * @throws Exception
	 */
	public void disconnect() throws Exception{
	
		logger.info("Disconnecting from [" + this.connectionUrl + "]");
		
		if(this.connection != null){
		   this.connection.close();	
		   this.connection = null;
		}else{
			  logger.info("Connection is null"); 
		}
		logger.info("Disconnected");
	}

	/**
	 * Get the data in the form of an has map of DTOs carrying words data
	 * @return the list of rows data
	 */
	public HashMap<String, String> getRows(HashMap<String, Word> synonyms) throws Exception{
		
		logger.info("Executing query: [" + this.query + "]");
		
		Statement statement = null;
		HashMap<String, String> rowsList = null;
		ResultSet resultSet = null;
		String rowValue = null;
		int loopCount = 0;
		Word synonymsWord = null;
		List<String> substringsToReplaceList = null;
		
		try{
			rowsList = new HashMap<String, String>();
		
		    statement = this.connection.createStatement();
		
		    statement.setQueryTimeout(this.queryTimeoutSeconds);
		    
		    resultSet = statement.executeQuery(this.query);
		    
		    logger.info("Query executed, now getting data");
		    
		    resultSet.beforeFirst();
		    
		    while(resultSet.next()){
		    	  
		    	  rowValue = resultSet.getString(1);
		    	  
		    	  if(rowValue != null){
		    		 rowValue = rowValue.trim();
		    		 
		    		 logger.info(loopCount + " >> row value loaded: [" + rowValue + "]");
		    		 if( (synonyms != null) && 
		    			 (synonyms.size() != 0) ){
		    		     for(String synonymReplacementToken: synonyms.keySet()){
		    		         synonymsWord = synonyms.get(synonymReplacementToken);
		    		         substringsToReplaceList = synonymsWord.getSynonyms();
		    		         for(String oneSubStringToReplace: substringsToReplaceList){
		    		        	 if(rowValue.indexOf(oneSubStringToReplace) != -1){
		    		    	        rowValue = rowValue.replaceAll(oneSubStringToReplace, synonymReplacementToken);
		    		    	        logger.info(loopCount + " >> row value synonym-ized: [" + rowValue + "] on [" + oneSubStringToReplace + "]=>[" + synonymReplacementToken + "]");   	 
		    		        	 }
		    		        }
		    		     }
		    		 }
		    		 rowsList.put(rowValue, "");
		    		 loopCount++;
		    	  }else{
		    		  	logger.error("Row is null, not saved");
		    	  }
		    }
		    logger.info("Data load completed, found " + loopCount + " database rows");
		}finally{
			     if(statement  != null){
			    	statement.close();
			     }
			     if(resultSet != null){
			    	resultSet.close();
				 }
		}
		return rowsList;
	}
}
